package br.com.egg.finances.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import br.com.egg.finances.business.dao.util.BDUtils;
import br.com.egg.finances.business.exception.BusinessErrorException;
import br.com.egg.finances.model.Crediario;
import br.com.egg.finances.model.Movimento;
import br.com.egg.finances.model.Parcela;
import br.com.egg.finances.model.TipoMovimento;

public class CrediarioDAO extends DefaultDAO<Crediario> {

	@Override
	public void insert(Crediario entity) {
		Connection connection = null;
		CallableStatement callableStatement = null;
		try {
			StringBuilder sbCall = new StringBuilder();
			sbCall.append("{ CALL inserir_crediario(?, ?, ?, ?, ?, ?, ?) }");
			
			String call = sbCall.toString();
			System.out.println(call);
			
			connection = BDUtils.getConnection();
			callableStatement = connection.prepareCall(call);
			callableStatement.setString(1, entity.getCdUsuario());
			callableStatement.setDate(2, new Date(entity.getDtPrimeiraParcela().getTime()));
			callableStatement.setInt(3, entity.getQtParcel());
			callableStatement.setDouble(4, entity.getVlTotal());
			callableStatement.setString(5, entity.getDeCrediario());
			callableStatement.setInt(6, entity.getApropriacaoCrediarioList().get(0).getCdCentroCusto());
			callableStatement.setString(7, entity.getApropriacaoCrediarioList().get(0).getCdCategoria());
			
			callableStatement.execute();
		} catch (Exception e) {
			throw new BusinessErrorException(e);
		} finally {
			BDUtils.closeResources(connection, callableStatement, null);
		}
	}

	@Override
	public void update(Crediario entity) {
		// TODO Auto-generated method stub
	}

	@Override
	public void delete(Crediario entity) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = BDUtils.getConnection();
			connection.setAutoCommit(false);
			
			StringBuilder sbDelete = new StringBuilder();
			sbDelete.append("DELETE FROM DEGGPARCELA WHERE CDUSUARIO = ? AND CDCREDIARIO = ? ");

			preparedStatement = connection.prepareStatement(sbDelete.toString());
			preparedStatement.setString(1, entity.getCdUsuario());
			preparedStatement.setInt(2, entity.getCdCrediario());
			
			System.out.println(preparedStatement);
			preparedStatement.executeUpdate();
			
			sbDelete = new StringBuilder();
			sbDelete.append("DELETE FROM DEGGAPROPRIACAOCREDIARIO WHERE CDUSUARIO = ? AND CDCREDIARIO = ? ");
			
			preparedStatement = connection.prepareStatement(sbDelete.toString());
			preparedStatement.setString(1, entity.getCdUsuario());
			preparedStatement.setInt(2, entity.getCdCrediario());
			
			System.out.println(preparedStatement);
			preparedStatement.executeUpdate();
			
			sbDelete = new StringBuilder();
			sbDelete.append("DELETE FROM DEGGCREDIARIO WHERE CDUSUARIO = ? AND CDCREDIARIO = ? ");
			
			preparedStatement = connection.prepareStatement(sbDelete.toString());
			preparedStatement.setString(1, entity.getCdUsuario());
			preparedStatement.setInt(2, entity.getCdCrediario());
			
			System.out.println(preparedStatement);
			preparedStatement.executeUpdate();
			
			connection.commit();
		} catch (Exception e) {
			try {
				connection.rollback();
			} catch (Exception e2) {
				System.err.println("NAO DEU ROLLBACK");
			}
			throw new BusinessErrorException(e);
		} finally {
			BDUtils.closeResources(connection, preparedStatement, null);
		}
	}

	@Override
	public Crediario find(Crediario entityFilter) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<Crediario> findAll(String cdUsuario) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<Crediario> crediarioList = new ArrayList<Crediario>();
		try {
			StringBuilder sbSelect = new StringBuilder();
			sbSelect.append("SELECT CRED.CDUSUARIO AS USUARIO, CRED.CDCREDIARIO AS CODIGO, CRED.DTPRIMEIRAPARCELA AS DATA, CRED.QTPARCELA AS QTPARC, CRED.VLTOTAL AS TOTAL, CRED.DECREDIARIO AS DESCR ");
			sbSelect.append("FROM DEGGCREDIARIO CRED ");
			sbSelect.append("WHERE CRED.CDUSUARIO = ? ");
			
			String select = sbSelect.toString();
			System.out.println(select);
			
			connection = BDUtils.getConnection();
			preparedStatement = connection.prepareStatement(select);
			preparedStatement.setString(1, cdUsuario);

			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				Crediario crediario = new Crediario();
				crediario.setCdUsuario(resultSet.getString("USUARIO"));
				crediario.setCdCrediario(resultSet.getInt("CODIGO"));
				crediario.setDtPrimeiraParcela(resultSet.getDate("DATA"));
				crediario.setQtParcel(resultSet.getInt("QTPARC"));
				crediario.setVlTotal(resultSet.getDouble("TOTAL"));
				crediario.setDeCrediario(resultSet.getString("DESCR"));
				
				crediarioList.add(crediario);
			}
			
			return crediarioList;
		} catch (Exception e) {
			throw new BusinessErrorException(e);
		} finally {
			BDUtils.closeResources(connection, preparedStatement, resultSet);
		}
	}
	
	public List<Parcela> findAllParcelas(String cdUsuario, Integer cdCrediario) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<Parcela> parcelaList = new ArrayList<Parcela>();
		try {
			StringBuilder sbSelect = new StringBuilder();
			sbSelect.append("SELECT PARC.CDUSUARIO AS USU, PARC.CDCREDIARIO AS CRED, PARC.NUPARCELA AS NUPARC, PARC.VLPARCELA AS VLPARC, PARC.DTVENCIMENTO AS DTVENC, PARC.FLPAGA AS FLPAGA ");
			sbSelect.append("FROM DEGGPARCELA PARC ");
			sbSelect.append("WHERE PARC.CDUSUARIO = ? AND PARC.CDCREDIARIO = ? ");
			
			String select = sbSelect.toString();
			System.out.println(select);
			
			connection = BDUtils.getConnection();
			preparedStatement = connection.prepareStatement(select);
			preparedStatement.setString(1, cdUsuario);
			preparedStatement.setInt(2, cdCrediario);

			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				Parcela parcela = new Parcela();
				parcela.setCdUsuario(resultSet.getString("USU"));
				parcela.setCdCrediario(resultSet.getInt("CRED"));
				parcela.setNuParcela(resultSet.getInt("NUPARC"));
				parcela.setVlParcela(resultSet.getDouble("VLPARC"));
				parcela.setDtVencimento(resultSet.getDate("DTVENC"));
				parcela.setFlPaga(Character.valueOf(resultSet.getString("FLPAGA").charAt(0)));
				
				parcelaList.add(parcela);
			}
			
			return parcelaList;
		} catch (Exception e) {
			throw new BusinessErrorException(e);
		} finally {
			BDUtils.closeResources(connection, preparedStatement, resultSet);
		}
	}
	
	public void pagarParcela(String cdUsuario, Integer cdCrediario, Integer nuParcela, String cdConta) {
		Connection connection = null;
		CallableStatement callableStatement = null;
		try {
			StringBuilder sbCall = new StringBuilder();
			sbCall.append("{ CALL pagar_parcela(?, ?, ?, ?) }");
			
			String call = sbCall.toString();
			
			connection = BDUtils.getConnection();
			callableStatement = connection.prepareCall(call);
			callableStatement.setString(1, cdUsuario);
			callableStatement.setInt(2, cdCrediario);
			callableStatement.setInt(3, nuParcela);
			callableStatement.setString(4, cdConta);
			
			System.out.println(callableStatement);
			callableStatement.execute();
		} catch (Exception e) {
			throw new BusinessErrorException(e);
		} finally {
			BDUtils.closeResources(connection, callableStatement, null);
		}
	}

}
